package projet.volleystat.bdd;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author Lameira Nelaupe Ferrand Fiore
 * @version 1.0
 * Creation des tables joueur, equipe et association dans une base de donnees nommee db.
 */
public class VolleyOpenhelper extends SQLiteOpenHelper {
	protected static final int DATABASE_VERSION = 4;
	protected static final String VOLLEY_BASE_NAME = "volley.db";

	protected static final String PLAYER_TABLE_NAME = "Player";
	public static final String Player_Columns_ID = "idPlayer";
	public static final String Player_Columns_NAME = "namePlayer";
	public static final String Player_Columns_FIRSTNAME = "firstnamePlayer";
	public static final String Player_Columns_NATIONALITY = "nationalityPlayer";

	protected static final String TEAM_TABLE_NAME = "Team";
	public static final String Team_Columns_ID = "idTeam";
	public static final String Team_Columns_NAME = "nameTeam";
	public static final String Team_Columns_COUNTRY = "country";

	protected static final String ASSOCIATION_TABLE_NAME = "Association";
	
	static final String STATISTIC_TABLE_NAME = "Statistic";
	public static final String Statistic_Columns_ID = "id";
	public static final String Statistic_Columns_IDMATCH = "idMatch";
	public static final String Statistic_Columns_IDACTION = "idAction";
	public static final String Statistic_Columns_IDTARGERPLAYER = "idTargerPlayer";
	public static final String Statistic_Columns_MARK = "mark";
	
	protected static final String QUERY_DB_CREATION_TEAM = "CREATE TABLE "
			+ TEAM_TABLE_NAME + " (" + Team_Columns_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + Team_Columns_NAME
			+ " TEXT NOT NULL, " + Team_Columns_COUNTRY + " TEXT NOT NULL);";

	protected static final String QUERY_DB_CREATION_PLAYER = "CREATE TABLE "
			+ PLAYER_TABLE_NAME + " (" + Player_Columns_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + Player_Columns_NAME
			+ " TEXT NOT NULL, " + Player_Columns_FIRSTNAME + " TEXT NOT NULL, "
			+ Player_Columns_NATIONALITY + " TEXT NOT NULL);";

	protected static final String QUERY_DB_CREATION_ASSOCIATION = "CREATE TABLE "
			+ ASSOCIATION_TABLE_NAME
			+ " ("
				+ Player_Columns_ID + " INTEGER, "
				+ Team_Columns_ID + " INTEGER,"
				+ "Foreign Key ("
					+ Player_Columns_ID
				+ ") references "
				+ PLAYER_TABLE_NAME
				+ "("
					+ Player_Columns_ID
				+ "),"
				+ "Foreign Key ("
					+ Team_Columns_ID
				+ ") references "
				+ TEAM_TABLE_NAME
				+ "("
					+ Team_Columns_ID
				+ ")" 
			+ ");";
	

	protected static final String QUERY_DB_CREATION_STATISTIQUE = "CREATE TABLE "
			+ STATISTIC_TABLE_NAME + " (" 
			+ Statistic_Columns_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
			+ Statistic_Columns_IDMATCH + " INTEGER," 
			+ Team_Columns_ID + " INTEGER," 
			+ Player_Columns_ID + " INTEGER," 
			+ Statistic_Columns_IDACTION + " INTEGER,"
			+ Statistic_Columns_IDTARGERPLAYER + " INTEGER,"
			+ Statistic_Columns_MARK + " INTEGER,"
			+ "Foreign Key ("+Player_Columns_ID+") references "+PLAYER_TABLE_NAME+"("+Player_Columns_ID+"),"
			+ "Foreign Key ("+Team_Columns_ID+") references "+TEAM_TABLE_NAME+"("+Team_Columns_ID+")"
			+ ");";


	/**
	 * @param context
	 * @param factory
	 * Construction de la base de donnees avec la version correspondante.
	 */
	public VolleyOpenhelper(Context context, CursorFactory factory) {
		super(context, VOLLEY_BASE_NAME, factory, DATABASE_VERSION);
	}

	
	/**
	 * @param db
	 * Creation de la base de donnees. Lors du premier insert la methode sera appelee automatiquement car la base de donnees n'existera pas.
	 */
	public void onCreate(SQLiteDatabase db) {
		System.out.println("Creation des tables");
		db.execSQL(QUERY_DB_CREATION_PLAYER);
		db.execSQL(QUERY_DB_CREATION_TEAM);
		db.execSQL(QUERY_DB_CREATION_ASSOCIATION);
		db.execSQL(QUERY_DB_CREATION_STATISTIQUE);
	}

	/**
	 * @param db
	 * @param oldVersion
	 * @param newVersion
	 * Mettre a jour des tables de la base de donnees en supprimant les anciennes tables et en les remplacant par les nouvelles.
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (newVersion > DATABASE_VERSION) {
			db.execSQL("DROP TABLE " + PLAYER_TABLE_NAME + ";");
			db.execSQL("DROP TABLE " + TEAM_TABLE_NAME + ";");
			db.execSQL("DROP TABLE " + ASSOCIATION_TABLE_NAME + ";");
			db.execSQL("DROP TABLE " + QUERY_DB_CREATION_STATISTIQUE + ";");
			onCreate(db);
		}
	}

}
